﻿using System;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.ComponentModel;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using GreenyMeter.DOM.Exceptions;


namespace GreenyMeter.DOM.DataBaseAcces
{
    /// <summary>
    /// Create to suport externally sql parameter on the application
    /// </summary>
    public class SqlDaAccesParameters
    {
        private string _paramName;

        public string ParamName
        {
            get { return _paramName; }
            set { _paramName = value; }
        }
        private object _paramValue;
        /// <summary>
        /// The value can be any object just be sure using the right one
        /// </summary>
        public object ParamValue
        {
            get { return _paramValue; }
            set { _paramValue = value; }
        }
        /// <summary>
        /// Initialice an instance of sql paramter
        /// </summary>
        /// <param name="paramName">Must be the parameter as crete in the store procedure or query Example: @Name</param>
        /// <param name="paramValue">Must contain the string, datetime, int, etc, that has the parameter value Example: "David"</param>
        public SqlDaAccesParameters(string paramName, object paramValue)
        {
            _paramName = paramName;
            _paramValue = paramValue;
        }
    }

    public class SqlDataAcces
    {

        private string _appConnectionString = ConfigurationManager.ConnectionStrings["verdesometro"].ConnectionString;

        protected SqlCommand GetNewCmd()
        {
            SqlCommand objCmd = new SqlCommand();
            objCmd.Connection = new SqlConnection(_appConnectionString);
            objCmd.CommandType = CommandType.StoredProcedure;
            return objCmd;

        }

        protected SqlCommand GetNewCmd(string CmdText)
        {
            SqlCommand objCmd = new SqlCommand(CmdText, new SqlConnection(_appConnectionString));
            objCmd.CommandType = CommandType.StoredProcedure;
            return objCmd;
        }

        protected SqlCommand GetNewCmd(string CmdText, string connectionName)
        {
            SqlCommand objCmd = new SqlCommand(CmdText, new SqlConnection(ConfigurationManager.ConnectionStrings[connectionName].ConnectionString));
            objCmd.CommandType = CommandType.StoredProcedure;
            return objCmd;
        }

        protected DataTable GetTable(SqlCommand objCmd)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter();

            try
            {
                da.SelectCommand = objCmd;
                da.Fill(dt);

                dt.DefaultView.AllowNew = false;
            }
            catch (Exception ex)
            {
                ExceptionUtility.LogException(ex, "SQLGetTable_" + objCmd.CommandText);
                throw;
            }
            finally
            {

                da.Dispose();
                da = null;

            }

            return dt;

        }

        #region QueryMethods
        /// <summary>
        /// Executes an Store Procedure with the parameters send in the list, those maybe in the same order as they are in the Store Procedure
        /// </summary>
        /// <param name="comandName">The Name of the Store Procedure to execute</param>
        /// <param name="sqlParams">The params into a list all in string format and in the same order as the store procedure expect them</param>
        /// <returns>DataTable with the info requested</returns>
        public DataTable GetDataTable(String comandName, List<SqlDaAccesParameters> sqlParams)
        {
            SqlCommand cmd = GetNewCmd(comandName);
            foreach (SqlDaAccesParameters param in sqlParams)
            {
                cmd.Parameters.Add(new SqlParameter(param.ParamName, param.ParamValue));
            }
            return GetTable(cmd);
        }

        public DataTable GetDataTable(String comandName, List<SqlDaAccesParameters> sqlParams, string connectionName)
        {
            SqlCommand cmd = GetNewCmd(comandName, connectionName);
            foreach (SqlDaAccesParameters param in sqlParams)
            {
                cmd.Parameters.Add(new SqlParameter(param.ParamName, param.ParamValue));
            }
            return GetTable(cmd);
        }

        private void GetDataSet(String comandName, List<SqlDaAccesParameters> sqlParams)
        {

        }

        public object ExecuteScalar(String comandName, List<SqlDaAccesParameters> sqlParams)
        {
            try
            {
                SqlCommand cmd = GetNewCmd(comandName);
                foreach (SqlDaAccesParameters param in sqlParams)
                {
                    cmd.Parameters.Add(new SqlParameter(param.ParamName, param.ParamValue));
                }
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                ExceptionUtility.LogException(ex, "SQLExecuteScalr_" + comandName);
                throw;
            }
        }

        private void ExecuteScalarGuid(String comandName, List<SqlDaAccesParameters> sqlParams)
        {

        }

        public void ExecuteNonQuery(String comandName, List<SqlDaAccesParameters> sqlParams)
        {
            SqlConnection cnn = null;
            try
            {
                cnn = new SqlConnection(_appConnectionString);
                SqlCommand cmd = new SqlCommand(comandName, cnn);
                cmd.CommandType = CommandType.StoredProcedure;
                foreach (SqlDaAccesParameters param in sqlParams)
                {
                    cmd.Parameters.Add(new SqlParameter(param.ParamName, param.ParamValue));
                }
                cnn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ExceptionUtility.LogException(ex, "SQLExecuteScalr_" + comandName);
                throw;
            }
            finally
            {
                if (cnn != null && cnn.State != ConnectionState.Closed)
                {
                    cnn.Close();
                }
            }
        }

        #endregion

    }
}